/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.JDBC;

import core.Shop;
import db.interfaces.ShopDAO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
/**
 *
 * @author Ahlberg
 * A class for fetching data from the school database.
 */
public class ShopJdbcDAO extends BaseJdbcDAO implements ShopDAO
{
    /**
     *
     * @param con
     */
    public ShopJdbcDAO(Connection con)
    {
        super(con);
    }
    /**
     * 
     * @param id
     * @return
     */
    public Shop getShopById(long id)
    {
        String sql = "SELECT * FROM Shops WHERE id = '" + id + "';";
        return createShop(sqlExecuteQuery(sql));
    }
    /**
     *
     * @param beginsWith
     * @return
     */
    public Collection<Shop> searchForShopsByName(String beginsWith)
    {
        return search(beginsWith, "name");
    }
    /**
     * 
     * @param beginsWith
     * @param fieldName
     * @param asc
     * @return
     */
    public Collection<Shop> searchForShopsByParameters(String beginsWith, String fieldName, boolean asc)
    {
        String sql = "";
        String sort = "ASC";
        if(!asc)
            sort = "DESC";
        if(fieldName.equals(""))
            sql = "SELECT * FROM Shops ORDER BY " + fieldName + " " + sort +";";
        else
            sql = "SELECT * FROM Shops WHERE " +
                "id LIKE '" + beginsWith + "%' OR " +
                "name LIKE '" + beginsWith + "%' ORDER BY " +
                fieldName + " " + sort +";";
        return createShopCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param beginsWith
     * @param field
     * @return
     */
    private Collection<Shop> search(String beginsWith, String field)
    {
        String sql = "SELECT * FROM Shops WHERE " + field + " LIKE '" + beginsWith + "%' ORDER BY " + field + " ASC;";
        return createShopCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param name
     * @return
     */
    public Collection<Shop> getShopsByName(String name)
    {
        if(name == null)
            return null;
        String sql = "SELECT * FROM Shops WHERE name='" + name + "' ORDER BY name ASC;";
        return createShopCollection(sqlExecuteQuery(sql));
    }
    /**
     *
     * @return
     */
    public Collection<Shop> getAllShops()
    {
        String sql = "SELECT * FROM Shops ORDER BY name ASC;";
        return createShopCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param shop
     * @return
     */
    public boolean containsShop(Shop shop)
    {
        if(shop == null)
            return false;
        String sql = "SELECT * FROM Shops WHERE id='" + shop.getId() + "';";
        return contains(sql);
    }
    /**
     * 
     * @param shop
     * @return
     */
    public boolean addShop(Shop shop)
    {
        if(shop == null)
            return false;
        String sql = "INSERT INTO Shops (name, description, weblink, XMLSource) " +
                           "VALUES ("
                           + "'" + shop.getName() + "', "
                           + "'" + shop.getDescription() + "', "
                           + "'" + shop.getWebLink() + "', "
                           + "'" + shop.getXMLSource() + "');";
        boolean b = add(sql);
        shop.setId(getLatestAddedId("Shops"));
        return b;
    }
    /**
     * 
     * @param shop
     * @return
     */
    public boolean editShop(Shop shop)
    {
        if(shop == null)
            return false;
        String sql = "UPDATE Shops SET  " +
                "name='" + shop.getName() +
                "', description = '" + shop.getDescription() +
                "', weblink = '" + shop.getWebLink() +
                "', XMLSource = '" + shop.getXMLSource() +
                "' WHERE id='" + shop.getId() + "';";
        return edit(sql);
    }
    /**
     * 
     * @param shop
     * @return
     */
    public boolean deleteShop(Shop shop)
    {
        if(shop == null)
            return false;
        String sql = "DELETE FROM Shops WHERE id = ' " + shop.getId() + "';";
        return delete(sql);
    }
    /**
     * 
     * @param rs
     * @return
     */
    private Shop createShop(ResultSet rs)
    {
        if(rs == null)
            return null;
        try{
            if(rs.next())
            {
                return new Shop(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getString("weblink"),
                        rs.getString("XMLSource"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 
     * @param rs
     * @return
     */
    private Collection<Shop> createShopCollection(ResultSet rs)
    {
        if(rs == null)
            return null;
        Collection<Shop> shops = new ArrayList<Shop>();

        Shop shop;
        while((shop = createShop(rs)) != null)
            shops.add(shop);
        return shops;
    }
}
